package api.service;

import api.model.ApiColumn;
import api.model.ApiTable;
import m.common.model.Model;
import m.common.model.TableMeta;
import m.common.model.config.ModelConfig;
import m.common.model.type.FieldType;
import m.common.model.util.*;
import m.common.service.Service;
import m.system.RuntimeData;
import m.system.cache.CacheUtil;
import m.system.db.*;
import m.system.exception.MException;
import m.system.util.ClassUtil;
import m.system.util.GenerateID;
import m.system.util.StringUtil;
import manage.util.DictionaryUtil;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;


public class ApiTableService extends Service {

    public String save(ApiTable model) throws Exception {
        String msg;
        TransactionManager tm=new TransactionManager();
        try {
            tm.begin(model.getOid());
            ModelCheckUtil.checkNotNull(model, "name","source","description");
            ModelCheckUtil.checkUniqueCombine(model,new String[]{"description"});
            ModelCheckUtil.checkUniqueCombine(model, new String[]{"name"});
            if(model.getSource().equals("B")){
                if (!model.getName().matches("[a-z_.0-9]*"))
                    throw new MException(this.getClass(), "表名只能包含小写字母数字和下划线");
            }else {
                if (!model.getName().matches("[a-z_0-9]*"))
                    throw new MException(this.getClass(), "表名只能包含小写字母数字和下划线");
            }
            DataSet ds=null;
            if(model.getSource().equals("A")){
                ModelCheckUtil.checkNotNull(model,"sourceSql");
            }else if(model.getSource().equals("B")){
                ds=getTableColumns(model.getName());
                if(ds.size()==0) throw new MException(this.getClass(),"表名错误或者不存在");
            }
            if(StringUtil.isSpace(model.getOid())){
                if(model.getSource().equals("Y")){
                    ModelCheckUtil.checkNotNull(model,"sourceClass");
                    TableMeta meta= ModelConfig.getTableMeta(ClassUtil.getClass(Model.class,model.getSourceClass()));
                    model.setOid(model.getSourceClass());
                    model.setSourceName(meta.name());
                    model.setSourceDesc(meta.description());
                    model.setIsinit("Y");
                }else{
                    if(model.getSource().equals("A")){
                        model.setOid("vtc_"+model.getName());
                    }else if(model.getSource().equals("B")){//已存在表
                        model.setOid(model.getName());
                    }else{
                        model.setOid("otc_"+model.getName());
                    }
                    if(model.getSource().equals("B")){//已存在表
                        model.setIsinit("Y");
                    }else {
                        model.setIsinit("N");
                        ApiColumn column = new ApiColumn();
                        column.setOid(GenerateID.generatePrimaryKey());
                        column.setTable(model);
                        column.setName("oid");
                        column.setDescription("主键");
                        column.setType(FieldType.STRING.toString());
                        column.setLength(50);
                        column.setNotnull("Y");
                        column.setIsinit("Y");
                        ModelUpdateUtil.insertModel(column);
                    }
                }
                ModelUpdateUtil.insertModel(model);
                msg="保存成功";
            }else{
                ModelUpdateUtil.updateModel(model, "description","sourceSql");
                msg="修改成功";
            }
            if(model.getSource().equals("B")){
                updateTableColumns(model,ds);//库表
            }
            DictionaryUtil.clear(ApiTable.class.getName());
            CacheUtil.clear(ApiTable.class);
            tm.commit();
        }catch (Exception e){
            tm.rollback();
            throw e;
        }
        return msg;
    }
    public DataSet getTableColumns(String name) throws SQLException {
        String[] tn=name.split("\\.");
        SqlBuffer sql=new SqlBuffer()
                .append("SELECT column_name name,data_type type,character_maximum_length length,")
                .append("case when is_nullable='NO' then 'Y' else 'N' end notnull,")
                .append("column_default defaultValue,column_comment description ")
                .append("FROM information_schema.columns ");
        if(tn.length>1){
            sql.append("WHERE table_schema = ? and table_name = ?",tn[0],tn[1]);
        }else{
            sql.append("WHERE table_name = ? and table_schema = ?",tn[0], RuntimeData.getTableSchema());
        }
        return sql.executeQuery();
    }
    public void updateTableColumns(ApiTable model,DataSet ds) throws SQLException, MException {
        List<ApiColumn> list = ModelQueryList.getModelList(ApiColumn.class, new String[]{"oid"}, null,
                QueryCondition.eq("table.oid", model.getOid()));
        ModelUpdateUtil.deleteModels(list.toArray(new ApiColumn[]{}));
        for(DataRow dr : ds.rows()){
            ApiColumn column = new ApiColumn();
            column.setOid(GenerateID.generatePrimaryKey());
            column.setTable(model);
            column.setName(dr.get(String.class,"name"));
            column.setField(dr.get(String.class,"name"));
            column.setDescription(dr.get(String.class,"description"));
            column.setType(getFieldType(dr.get(String.class,"type")));
            column.setLength(dr.get(Integer.class,"length"));
            column.setNotnull(dr.get(String.class,"notnull"));
            column.setIsinit("Y");
            ModelUpdateUtil.insertModel(column);
        }
        DictionaryUtil.clear(ApiColumn.class.getName());
        CacheUtil.clear(ApiColumn.class);
    }
    private String getFieldType(String t){
        if(t.contains("int")){
            return "INT";
        }else if(t.contains("float")||t.contains("double")||t.contains("decimal")){
            return "DOUBLE";
        }else if(t.contains("date")||t.contains("time")){
            return "DATE";
        }else{
            return "STRING";
        }
    }
    public String delete(ApiTable model) throws Exception {
        String msg;
        TransactionManager tm=new TransactionManager();
        try {
            tm.begin(model.getOid());
            model=ModelQueryList.getModel(model);
            //已初始化不能删除
            if(model.getIsinit().equals("Y")) throw new MException(this.getClass(),"已初始化不能删除");
            if(model.getSource().equals("N")) {//自建删除明细
                List<ApiColumn> list = ModelQueryList.getModelList(ApiColumn.class, new String[]{"oid"}, null,
                    QueryCondition.eq("table.oid", model.getOid()));
                ModelUpdateUtil.deleteModels(list.toArray(new ApiColumn[]{}));
            }
            ModelUpdateUtil.deleteModel(model);
            msg="删除成功";
            tm.commit();
        }catch (Exception e){
            tm.rollback();
            throw e;
        }
        return msg;
    }
    public String init(ApiTable model) throws Exception {
        String msg;
        TransactionManager tm=new TransactionManager();
        try {
            tm.begin(model.getOid());
            model=ModelQueryList.getModel(model);
            if(!(model.getSource().equals("N")||model.getSource().equals("A"))) throw new MException(this.getClass(),"只有自建表和视图才需要初始化");
            String schema=ModelConfig.getTableSchema();
            if(StringUtil.isSpace(schema)) throw new MException(this.getClass(),"请修改数据库配置table_schema");
            if(model.getSource().equals("N")) {
                DataSet ds = DBManager.executeQuery("select count(*) num from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA=? and TABLE_NAME=?",
                        new String[]{schema, model.getOid()});
                if (ds.size() == 0 || ds.get(Long.class, 0, "num") == 0) {
                    String sql = "CREATE TABLE " + schema + "." + model.getOid() + " (" +
                            " oid VARCHAR(50) NOT NULL COMMENT 'oid主键', " +
                            "PRIMARY KEY (oid) " +
                            " ) " + "COMMENT='" + model.getDescription() + "' CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci";
                    DBManager.executeUpdate(sql);
                }
                List<ApiColumn> list = ModelQueryList.getModelList(ApiColumn.class, new String[]{"*"}, null,
                        QueryCondition.and(
                                QueryCondition.eq("isinit", "N"),
                                QueryCondition.eq("table.oid", model.getOid())
                        )
                );
                model.setIsinit("Y");
                ModelUpdateUtil.updateModel(model, "isinit");
                for (ApiColumn col : list) {
                    String sql = "ALTER TABLE " + schema + "." + model.getOid() +
                            " ADD COLUMN " + col.getName() + " " + getDatabaseType(col.getType(), col.getLength()) +
                            (col.getNotnull().equals("Y") ? " NOT NULL" : " NULL") + " COMMENT '" + col.getDescription() + "'";
                    DBManager.executeUpdate(sql);
                    col.setIsinit("Y");
                }
                ModelUpdateUtil.updateModels(list.toArray(new ApiColumn[]{}), "isinit");
            }else if(model.getSource().equals("A")){
                StringBuilder sql= new StringBuilder().append("CREATE OR REPLACE VIEW ")
                        .append(schema).append(".").append(model.getOid())
                        .append(" AS ").append(model.getSourceSql());
                try {
                    DBManager.executeUpdate(sql.toString());
                    model.setIsinit("Y");
                    ModelUpdateUtil.updateModel(model, "isinit");
                    List<ApiColumn> list = ModelQueryList.getModelList(ApiColumn.class, new String[]{"*"}, null,
                        QueryCondition.and(
                            QueryCondition.eq("isinit", "N"),
                            QueryCondition.eq("table.oid", model.getOid())
                        )
                    );
                    for (ApiColumn col : list) {
                        col.setIsinit("Y");
                    }
                    ModelUpdateUtil.updateModels(list.toArray(new ApiColumn[]{}), "isinit");
                } catch (SQLException e) {
                    throw new MException(ModelUtil.class,"创建视图时SQL出错!"+e.getMessage());
                }
            }
            msg="初始化成功";
            tm.commit();
        }catch (Exception e){
            tm.rollback();
            throw e;
        }
        return msg;
    }
    private String getDatabaseType(String type,Integer len){
        switch (type) {
            case "STRING":
            case "MODEL":
            case "TABLE":
                return "VARCHAR(" + len + ")";
            case "INT":
            case "DOUBLE":
                return type;
            case "DATE":
                return "DATETIME";
            default:
                return "CHAR(1)";
        }
    }

    /**
     * 插入或更新table模型，data[oid]为空则插入，否则查找是否存在该记录
     */
    public boolean saveModel(String tableOid, Map<String,Object> data) throws Exception {
        return saveModel(tableOid,data,null);
    }

    /**
     * 插入或更新table模型，data[oid]为空则插入，否则查找是否存在该记录， fields指定更新操作时的列
     * 返回true插入 false更新
     */
    public boolean saveModel(String tableOid, Map<String,Object> data,String... fields) throws Exception {
        String oid= (String) data.get("oid");
        if(StringUtil.isSpace(oid)){
            data.put("oid",GenerateID.generatePrimaryKey());
            TableUpdateUtil.insertModel(tableOid,data);
            return true;
        }else{
            List<Map<String,Object>> list=TableQueryList.getList(tableOid,new String[]{"oid"},null,QueryCondition.eq("oid",oid));
            if(!list.isEmpty()){
                TableUpdateUtil.updateModel(tableOid,data,fields);
                return false;
            }else{
                TableUpdateUtil.insertModel(tableOid,data);
                return true;
            }
        }
    }
}
